﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Schema;
using NPOI.SS.UserModel;

namespace Office.Automatic.Library.Data.Utils.Excel
{
    internal class Importer
    {
        private class RowParser<TEntity> where TEntity : new()
        {
            private readonly ImportSetting _setting;
            private readonly string?[]? _header;
            private readonly int _columnStartIndex;

            private class PropertyExt
            {
                private readonly PropertyInfo _propertyInfo;
                private readonly Action<TEntity, PropertyInfo, ICell?, string?> _setValueAction;
                private readonly ImportSetting _setting;
                private readonly string? _annotation;

                public PropertyExt(PropertyInfo propertyInfo, ImportSetting setting, string? annotation)
                {
                    _propertyInfo = propertyInfo;
                    _setting = setting;
                    _annotation = annotation;

                    var type = propertyInfo.PropertyType;
                    if (type == typeof(string))
                    {
                        _setValueAction = this._SetStringValue;
                        return;
                    }
                    else if (type == typeof(int))
                    {
                        _setValueAction = this._SetIntValue;
                        return;
                    }
                    else if (type == typeof(long))
                    {
                        _setValueAction = this._SetLongValue;
                        return;
                    }
                    else if (type == typeof(float))
                    {
                        _setValueAction = this._SetSingleValue;
                        return;
                    }
                    else if (type == typeof(double))
                    {
                        _setValueAction = this._SetDoubleValue;
                        return;
                    }
                    else if (type == typeof(decimal))
                    {
                        _setValueAction = this._SetDecimalValue;
                        return;
                    }
                    else if (type == typeof(DateTime))
                    {
                        _setValueAction = this._SetDateTimeValue;
                        return;
                    }
                    else
                    {
                        throw new ArgumentException("Property type not supported.", nameof(propertyInfo));
                    }
                }

                public void SetData(TEntity obj, ICell? cell)
                {
                    _setValueAction(obj, _propertyInfo, cell, _annotation);
                }

                private static object? GetCellValue(ICell? cell, out CellDataType dataType)
                {
                    if (cell == null)
                    {
                        dataType = CellDataType.String;
                        return null;
                    }

                    var cellType = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;

                    switch (cellType)
                    {
                        case CellType.Numeric:
                            dataType = CellDataType.Numeric;
                            return cell.NumericCellValue;
                        case CellType.String:
                            dataType = CellDataType.String;
                            return cell.StringCellValue;
                        case CellType.Boolean:
                            dataType = CellDataType.Boolean;
                            return cell.BooleanCellValue;
                        default:
                            dataType = CellDataType.Null;
                            return null;
                    }
                }

                private void _SetStringValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {
                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity, _setting.GetConverter().ToText(data, dataType, annotation));
                }

                private void _SetIntValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null ? 0 : _setting.GetConverter().ToInt(data, dataType, annotation));
                }

                private void _SetLongValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null ? 0L : _setting.GetConverter().ToLong(data, dataType, annotation));
                }

                private void _SetSingleValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null ? 0.0F : _setting.GetConverter().ToSingle(data, dataType, annotation));
                }

                private void _SetDoubleValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null ? 0.0D : _setting.GetConverter().ToDouble(data, dataType, annotation));
                }

                private void _SetDecimalValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null
                            ? decimal.MinValue
                            : _setting.GetConverter().ToDecimal(data, dataType, annotation));
                }

                private void _SetDateTimeValue(TEntity entity, PropertyInfo property, ICell? cell, string? annotation)
                {

                    var data = GetCellValue(cell, out var dataType);
                    property.SetValue(entity,
                        data == null
                            ? DateTime.MinValue
                            : _setting.GetConverter().ToDateTime(data, dataType, annotation));
                }
            }

            private readonly Dictionary<int, PropertyExt> _colIndexFieldDataDic = new Dictionary<int, PropertyExt>();
            private readonly Dictionary<string, PropertyExt> _colNameFieldDataDic = new Dictionary<string, PropertyExt>();

            private readonly Dictionary<string, PropertyExt> _propertyNameFieldDataDic =
                new Dictionary<string, PropertyExt>();


            public RowParser(ImportSetting setting, string?[]? header, int columnStartIndex = -1)
            {
                _setting = setting;
                _header = header;
                _columnStartIndex = columnStartIndex;
                Init();
            }

            private void Init()
            {
                var entityType = typeof(TEntity);
                foreach (var propertyInfo in entityType.GetProperties())
                {
                    var attr = propertyInfo.GetCustomAttribute<ColumnAttribute>();
                    
                    var f = new PropertyExt(propertyInfo, _setting, attr?.Annotation);

                    if (attr != null)
                    {
                        if (attr.Index > -1)
                            _colIndexFieldDataDic.Add(attr.Index, f);

                        if (attr.Name != null)
                            _colNameFieldDataDic.Add(attr.Name, f);
                    }

                    _propertyNameFieldDataDic.Add(propertyInfo.Name, f);
                }
            }

            public TEntity Read(IRow row)
            {
                var useHeader = _header != null;

                var startIndex = _columnStartIndex > 0 && _header != null ? _columnStartIndex : 0;
                var maxIndex = _columnStartIndex > 0 && _header != null
                    ? _columnStartIndex + _header.Length
                    : row.LastCellNum;

                var entity = new TEntity();

                for (var i = startIndex; i < maxIndex; i++)
                {
                    var cell = row.GetCell(i);

                    if (_colIndexFieldDataDic.ContainsKey(i))
                    {
                        _colIndexFieldDataDic[i].SetData(entity, cell);
                        continue;
                    }

                    if (!useHeader) continue;

                    var headIndex = i - startIndex;
                    if (headIndex < _header.Length)
                    {
                        var h = _header[headIndex];
                        if (string.IsNullOrEmpty(h)) continue;

                        if (_colNameFieldDataDic.ContainsKey(h))
                        {
                            _colNameFieldDataDic[h].SetData(entity, cell);
                            continue;
                        }

                        if (_propertyNameFieldDataDic.ContainsKey(h))
                        {
                            _propertyNameFieldDataDic[h].SetData(entity, cell);
                            continue;
                        }
                    }
                }

                return entity;
            }
        }

        private static readonly ImportSetting BasicImportSetting = new ImportSetting()
        {
            Converter = new DataConverter()
        };

        private static ImportSetting ValidateSetting(ImportSetting? setting)
        {
            if (setting == null)
            {
                return BasicImportSetting;
                ;
            }

            setting.Converter ??= new DataConverter();

            return setting;
        }

        private static string?[] AcquireHeader(NPOI.SS.UserModel.IRow row, out int startColumnIndex,
            ImportSetting setting)
        {
            var result = new List<string?>();
            startColumnIndex = row.FirstCellNum;
            for (var cellIndex = startColumnIndex; cellIndex < row.LastCellNum; cellIndex++)
            {
                var cell = row.GetCell(cellIndex);
                if (cell == null)
                {
                    result.Add(null);
                }
                else
                {
                    result.Add(cell.ReadCellValue<string>(setting.GetConverter(), null));
                }
            }

            return result.ToArray();
        }

        internal static TEntity[] Import<TEntity>(string file, ImportSetting? setting=null) where TEntity :class, new()
        {
            var attr = typeof(TEntity).GetCustomAttribute<SheetAttribute>();
            if (attr == null)
                throw new InvalidOperationException("Sheet Attribute not found on provided Generic Type.");

            return Import<TEntity>(file, attr.Name, attr.DataRowIndex, attr.HasHeadRow, attr.HeadRowIndex, attr.LastDataRowIndex, setting);
        }

        internal static TEntity[] Import<TEntity>(string file, string sheetName, int dataRowIndex = -1,
            bool hasHeadRow = false, int headRowIndex = -1, int maxRowIndex=-1, ImportSetting? setting = null) where TEntity : class, new()
        {
            setting = ValidateSetting(setting);

            var list = new List<TEntity>();
            using var stream = File.Open(file, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            var workbook = Path.GetExtension(file).ToLower() == ".xls"
                ? NPOI.SS.UserModel.WorkbookFactory.Create(stream)
                : new NPOI.XSSF.UserModel.XSSFWorkbook(stream);

            var sheet = workbook.GetSheet(sheetName);

            if (sheet.LastRowNum <= dataRowIndex) return list.ToArray();

            string?[]? header = null;
            var startColumnIndex = -1;

            if (hasHeadRow)
            {
                headRowIndex = headRowIndex == -1 ? 0 : headRowIndex;
                header = AcquireHeader(sheet.GetRow(headRowIndex), out startColumnIndex, setting);
            }

            var rowReader = new RowParser<TEntity>(setting, header, startColumnIndex);
            dataRowIndex = dataRowIndex == -1 ? (hasHeadRow ? headRowIndex + 1 : 0) : dataRowIndex;

            for (var rowIndex = dataRowIndex; rowIndex <= sheet.LastRowNum && rowIndex < maxRowIndex; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                list.Add(rowReader.Read(row));
            }

            return list.ToArray();
        }
    }
}
